package com.atguigu.jdbc;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*
    通过JDBC对数据库进行增，删，改，查的操作
 */
public class CRUDDemo {

    /*
    CREATE TABLE emp(
    id INT,
    NAME VARCHAR(20)
    );

        向表中添加数据
     */
    @Test
    public void test() throws SQLException {
        //1.获取连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.预编译 - ?叫作占位符
        String sql = "insert into emp(id,name) values(?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        //3.给占位符赋值
        /*
            setInt(int parameterIndex, int x)
            parameterIndex : 第几个占位符
            x : 具体的数据
         */
        ps.setInt(1,100);
        ps.setString(2,"longge");
        //4.执行sql语句
        //返回值 ：受到影响的数据的条数
        int i = ps.executeUpdate();//该方法用来执行 增，删，改的sql语句
        System.out.println("有" + i + "条数据受到影响");
        //5.关资源
        JDBCUtils.close(ps,connection);
    }

    /*
        修改数据
     */
    @Test
    public void test2() throws SQLException {
        //1.获取连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.预编译
        String sql = "update emp set name=? where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        //3.给占位符赋值
        ps.setString(1,"zhiling");
        ps.setInt(2,100);
        //4.执行sql语句
        int i = ps.executeUpdate();
        System.out.println("有" + i + "条数据受到影响");
        //5.关资源
        JDBCUtils.close(ps,connection);
    }
    /*
        删除表中数据
     */
    @Test
    public void test3() throws SQLException {
        //1.获取连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.预编译
        String sql = "delete from emp";
        PreparedStatement ps = connection.prepareStatement(sql);
        //3.执行sql语句
        int i = ps.executeUpdate();
        System.out.println("有" + i + "条数据受到影响");;
        //4.关资源
        JDBCUtils.close(ps,connection);
    }

    /*
        查询
     */
    @Test
    public void test4() throws SQLException {
        List<Employee> resultSet = getResultSet();

        for (Employee e : resultSet) {
            System.out.println(e.getId() + "===" + e.getName());
        }
    }

    /*
        获取表中所有的数据
     */
    private List<Employee> getResultSet() throws SQLException {
        List<Employee> list = new ArrayList<>();

        //1.获取连接
        Connection connection = JDBCUtils.getConnection();
        //2.预编译
        String sql = "select id,name from emp";
        PreparedStatement ps = connection.prepareStatement(sql);
        //3.执行sql
        ResultSet rs = ps.executeQuery();//executeQuery执行查询的sql语句
        //4.遍历ResultSet
        while(rs.next()){//是否有下一条数据
            /*
                getInt(int columnIndex) : 根据字段的位置获取内容
                getInt(String columnLabel) ：根据字段的名字获取内容
             */
            int id = rs.getInt("id");
            String name = rs.getString("name");

            Employee employee = new Employee(id, name);
            list.add(employee);
        }
        //5.关资源
        JDBCUtils.close(ps,connection,rs);

        return list;
    }

}
